﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using Model;

namespace LogisticsDAL
{
    public class CarresssCyDAL
    {
        /// <summary>
        /// 承运单管理主页信息
        /// </summary>
        /// <returns></returns>
        public DataTable sqlCarrierList(int pageIndex,int pageSize,out int Count,Carriers cars)
        {
            string TableName = "View_CarriesQuan";
            string Pramary = "CarriesID";
            string Fild = "1=1";
            if (cars.CarriesID != "")
            {
                Fild = Fild + " and CarriesID='" + cars.CarriesID + "'";
            }
            if (cars.LeaverDate != "" || cars.zhiDate != "")
            {
                Fild = Fild + " and (LeaverDate>='" + cars.LeaverDate + "' and LeaverDate<='"+cars.zhiDate+"')";
            }
            if (!string.IsNullOrWhiteSpace(cars.ReceiveLinkman))
            {
                Fild = Fild + " and ReceiveLinkman like '%" + cars.ReceiveLinkman + "%'";
            }
            if (!string.IsNullOrWhiteSpace(cars.SendLinkman))
            {
                Fild = Fild + " and SendLinkman like '%" + cars.SendLinkman + "%'";
            }
            DataTable tab = PROC_Helper.Paging(TableName, Pramary, pageIndex, pageSize, Fild, out Count);
            return tab;
        }
        

        
        /// <summary>
        /// 承运单签收页面
        /// </summary>
        /// <returns></returns>
        public DataTable sqlCarrierQian(int pageIndex, int pageSize, out int Count, Carriers cars)
        {
            string TableName = "View_CarriesQuan";
            string Pramary = "CarriesID";
            string Fild = "FinishedState<2";
            if (cars.CarriesID != "")
            {
                Fild = Fild + " and CarriesID='" + cars.CarriesID + "'";
            }
            if (cars.LeaverDate != "" || cars.zhiDate != "")
            {
                Fild = Fild + " and (LeaverDate>='" + cars.LeaverDate + "' and LeaverDate<='" + cars.zhiDate + "')";
            }
            if (!string.IsNullOrWhiteSpace(cars.ReceiveLinkman))
            {
                Fild = Fild + " and ReceiveLinkman like '%" + cars.ReceiveLinkman + "%'";
            }
            if (!string.IsNullOrWhiteSpace(cars.SendLinkman))
            {
                Fild = Fild + " and SendLinkman like '%" + cars.SendLinkman + "%'";
            }
            DataTable tab = PROC_Helper.Paging(TableName, Pramary, pageIndex, pageSize, Fild, out Count);
            return tab;
        }

        /// <summary>
        /// 条件查询
        /// </summary>
        /// <returns></returns>
        public List<Carriers> CarriesBind(Carriers cars)
        {
            StringBuilder sql =new StringBuilder("SELECT [CarriesID],[SendCompany],[SendAddress] ,[SendLinkman],[ReceiveCompany],[FK_ReceiveAddress] ,[ReceiveLinkman] ,[LeaverDate],[ReceiveDate],[FinishedState],[InsuranceCost],[TransportCost],[OtherCost],[TotalCost],[FK_UserID] FROM [Carriers] ");
            List<string> wherelist = new List<string>();
            List<SqlParameter> pramlist = new List<SqlParameter>();
            if (cars.CarriesID!=""){
                wherelist.Add("CarriesID=@Carriesd");
                pramlist.Add(new SqlParameter("@Carriesd",cars.CarriesID));
            }
            if (cars.LeaverDate!="" || cars.zhiDate != "")
            {
                wherelist.Add("LeaverDate>=@LeaverDate and LeaverDate<=@zhidao");
                pramlist.Add(new SqlParameter("@LeaverDate", cars.LeaverDate));
                pramlist.Add(new SqlParameter("@zhidao", cars.zhiDate));
            }
            if (!string.IsNullOrWhiteSpace(cars.ReceiveLinkman))
            {
                wherelist.Add("ReceiveLinkman=@ReceiveLinkman");
                pramlist.Add(new SqlParameter("@ReceiveLinkman", cars.ReceiveLinkman));
            }
            if (!string.IsNullOrWhiteSpace(cars.SendLinkman))
            {
                wherelist.Add("SendLinkman=@SendLinkman");
                pramlist.Add(new SqlParameter("@SendLinkman", cars.SendLinkman));
            }
            if (wherelist.Count > 0)
            {
                string str = string.Join(" and ", wherelist.ToArray());
                sql.Append(" where " + str);
            }
            SqlDataReader read = DBHelper.getExreaders(sql.ToString(), pramlist.ToArray());
            List<Carriers> lis = new List<Carriers>();
            
            if (read.HasRows)
            {
                while (read.Read())
                {
                    Carriers carres = new Carriers();
                    carres.CarriesID =read["CarriesID"].ToString();
                    carres.LeaverDate = read["LeaverDate"].ToString();
                    carres.zhiDate= read["LeaverDate"].ToString();
                    carres.SendLinkman = read["SendLinkman"].ToString();
                    carres.SendCompany = read["SendCompany"].ToString();
                    carres.SendAddress = read["SendAddress"].ToString();
                    carres.ReceiveLinkman = read["ReceiveLinkman"].ToString();
                    carres.ReceiveCompany = read["ReceiveCompany"].ToString();
                    carres.TotalCost = read["TotalCost"].ToString();
                    carres.FinishedState = Convert.ToInt32(read["FinishedState"]);
                    lis.Add(carres);
                }
            }

            return lis;
            
        }


        /// <summary>
        /// 删除承运单
        /// </summary>
        /// <param name="carID"></param>
        /// <returns></returns>
        public int DelCarrier(int carID)
        {
            string sql = "p_delCarr";
            SqlParameter[] pram =
            {
                new SqlParameter("@CarriesID",carID)
            };
            int result = DBHelper.Number(sql, pram);
            return result;
        }
        public int PDcarrID(int carID)
        {
            string sql = "SELECT COUNT(1) FROM dbo.Carriers WHERE CarriesID=@carriesid";
            SqlParameter[] pram =
            {
                new SqlParameter("@carriesid",carID)
            };
            return Convert.ToInt32(DBHelper.GetObject(sql, pram));
        }
        /// <summary>
        /// 判断任务调度表有没有信息
        /// </summary>
        /// <param name="carrid"></param>
        /// <returns></returns>
        public int PDsching(int carrid)
        {
            string sql = "SELECT COUNT(1) FROM dbo.Scheduling WHERE FK_CarriersID=@carrierID";
            SqlParameter[] pram =
            {
                new SqlParameter("@carrierID",carrid)
            };
            return Convert.ToInt32(DBHelper.GetObject(sql, pram));
        }

        /// <summary>
        /// 添加承运单
        /// </summary>
        /// <param name="carers"></param>
        /// <returns></returns>
        public int InsetCarries(Carriers carers)
        {
            string sql = @"insert into Carriers values(@SendCompany,@SendAddress,@SendLinkman,@SendPhone,@ReceiveCompany,
                @FK_ReceiveAddress,@ReceiveLinkman,@ReceivePhone,@LeaverDate,null,
                0,@InsuranceCost,@TransportCost,@OtherCost,@TotalCost,@Remark,@FK_UserID,GETDATE(),null,null)";
            SqlParameter[] pram =
            {
                new SqlParameter("@SendCompany",carers.SendCompany),
                new SqlParameter("@SendAddress",carers.SendAddress),
                new SqlParameter("@SendLinkman",carers.SendLinkman),
                new SqlParameter("@SendPhone",carers.SendPhone),
                new SqlParameter("@ReceiveCompany",carers.ReceiveCompany),
                new SqlParameter("@FK_ReceiveAddress",carers.ReceiveAddress),
                new SqlParameter("@ReceiveLinkman",carers.ReceiveLinkman),
                new SqlParameter("@ReceivePhone",carers.ReceivePhone),
                new SqlParameter("@LeaverDate",carers.LeaverDate),
                new SqlParameter("@InsuranceCost",carers.InsuranceCost),
                new SqlParameter("@TransportCost",carers.TransportCost),
                new SqlParameter("@OtherCost",carers.OtherCost),
                new SqlParameter("@TotalCost",carers.TotalCost),
                new SqlParameter("@Remark",carers.Remark),
                new SqlParameter("@FK_UserID",carers.UserID)
            };
            int result = DBHelper.Numbers(sql, pram);
            return result;
        }
        /// <summary>
        /// 判断登录用户的角色
        /// </summary>
        /// <param name="crs"></param>
        /// <returns></returns>
        public int RoleName(int usid)
        {
            string sql = "SELECT COUNT(1) FROM dbo.Role WHERE RoleID=(SELECT FK_RoleID FROM dbo.UserInfo WHERE UserID=@Userid) AND (RoleName='承运业务员' OR RoleName='系统管理员')";
            SqlParameter[] pram =
            {
                new SqlParameter("@Userid",usid)
            };
            int result =Convert.ToInt32(DBHelper.GetObject(sql, pram));
            return result;
        }


        /// <summary>
        /// 承运单详情页面
        /// </summary>
        /// <param name="caris"></param>
        /// <returns></returns>
        public Carriers CarriesDecial(int CarriesID)
        {
            string sql = "p_CarrierDeial";
            SqlParameter[] pram =
            {
                new SqlParameter("@CarriesID",CarriesID)
            };
            Carriers cers = new Carriers();
            SqlDataReader read = DBHelper.Exreader(sql, pram);
            if (read.HasRows)
            {
                while (read.Read())
                {
                    
                    cers.CarriesID = read["CarriesID"].ToString();
                    cers.LeaverDate = read["LeaverDate"].ToString();
                    cers.ReceiveDate = read["ReceiveDate"].ToString();
                    cers.zhiDate = read["LeaverDate"].ToString();
                    cers.SendLinkman = read["SendLinkman"].ToString();
                    cers.SendCompany = read["SendCompany"].ToString();
                    cers.SendAddress = read["SendAddress"].ToString();
                    cers.ReceiveLinkman = read["ReceiveLinkman"].ToString();
                    cers.ReceiveCompany = read["ReceiveCompany"].ToString();
                    cers.ReceiveAddress = read["FK_ReceiveAddress"].ToString();
                    cers.TotalCost = read["TotalCost"].ToString();
                    cers.InsuranceCost = read["InsuranceCost"].ToString();
                    cers.TransportCost = read["TransportCost"].ToString();
                    cers.OtherCost = read["OtherCost"].ToString();
                    cers.SendPhone = read["SendPhone"].ToString();
                    cers.ReceivePhone = read["ReceivePhone"].ToString();
                    cers.FinishedState = Convert.ToInt32(read["FinishedState"]);
                    cers.Remark = read["Remark"].ToString();
                    cers.UserName = read["UserName"].ToString();
                }
                read.Close();
            }
            return cers;
        }

        /// <summary>
        /// 显示货物
        /// </summary>
        /// <param name="carrid"></param>
        /// <returns></returns>
        public SqlDataReader readCargo(int carrid)
        {
            string sql = "SELECT * FROM dbo.Goods WHERE FK_CarriersID=@Carries";
            SqlParameter[] pram =
            {
                new SqlParameter("@Carries",carrid)
            };
            return DBHelper.getExreaders(sql, pram);
        }

        /// <summary>
        /// 添加货物
        /// </summary>
        public int GoodsInsert(Goods god)
        {
            string sqlproc = "p_Insert_Goods";
            SqlParameter[] pram =
            {
                new SqlParameter("@goodName",god.GoodsName),
                new SqlParameter("@Amount",god.Amount),
                new SqlParameter("@Weight",god.Weight),
                new SqlParameter("@Volume",god.Volume),
                new SqlParameter("@CarriersID",god.FK_CarriersID)
            };
            return DBHelper.Number(sqlproc, pram);
        }



        /// <summary>
        /// 修改承运单表
        /// </summary>
        /// <param name="carers"></param>
        /// <returns></returns>
        public int UpdateCarres(Carriers carers)
        {
            string sqlproc = "p_Update_Carriers";
            SqlParameter[] pram =
            {
                new SqlParameter("@CarriesID",carers.CarriesID),
                 new SqlParameter("@SendCompany",carers.SendCompany),
                new SqlParameter("@SendAddress",carers.SendAddress),
                new SqlParameter("@SendLinkman",carers.SendLinkman),
                new SqlParameter("@SendPhone",carers.SendPhone),
                new SqlParameter("@ReceiveCompany",carers.ReceiveCompany),
                new SqlParameter("@FK_ReceiveAddress",carers.ReceiveAddress),
                new SqlParameter("@ReceiveLinkman",carers.ReceiveLinkman),
                new SqlParameter("@ReceivePhone",carers.ReceivePhone),
                new SqlParameter("@InsuranceCost",carers.InsuranceCost),
                new SqlParameter("@TransportCost",carers.TransportCost),
                new SqlParameter("@OtherCost",carers.OtherCost)
            };
            int result = DBHelper.Number(sqlproc, pram);
            return result;
        }


        public List<Carriers> CarriesSelectDispaly(Carriers cars)
        {
            StringBuilder sql = new StringBuilder("SELECT [CarriesID],[SendCompany],[SendAddress] ,[SendLinkman],[ReceiveCompany],[FK_ReceiveAddress] ,[ReceiveLinkman] ,[LeaverDate],[ReceiveDate],[FinishedState],[InsuranceCost],[TransportCost],[OtherCost],[TotalCost],[FK_UserID],UserName FROM [Carriers] c inner join UserInfo u on c.FK_UserID=u.UserID ");
            //string sql = "p_Carriesquan";
            List<string> wherelist = new List<string>();
            List<SqlParameter> pramlist = new List<SqlParameter>();
            if (cars.CarriesID != "")
            {
                wherelist.Add("CarriesID=@Carriesd");
                pramlist.Add(new SqlParameter("@Carriesd", cars.CarriesID));
            }
            if (cars.LeaverDate != "" || cars.zhiDate != "")
            {
                wherelist.Add("LeaverDate>=@LeaverDate and LeaverDate<=@zhidao");
                pramlist.Add(new SqlParameter("@LeaverDate", cars.LeaverDate));
                pramlist.Add(new SqlParameter("@zhidao", cars.zhiDate));
            }
            if (!string.IsNullOrWhiteSpace(cars.ReceiveLinkman))
            {
                wherelist.Add("ReceiveLinkman=@ReceiveLinkman");
                pramlist.Add(new SqlParameter("@ReceiveLinkman", cars.ReceiveLinkman));
            }
            if (!string.IsNullOrWhiteSpace(cars.SendLinkman))
            {
                wherelist.Add("SendLinkman=@SendLinkman");
                pramlist.Add(new SqlParameter("@SendLinkman", cars.SendLinkman));
            }
            if (wherelist.Count > 0)
            {
                string str = string.Join(" and ", wherelist.ToArray());
                sql.Append(" where " + str);
            }
            SqlDataReader read = DBHelper.getExreaders(sql.ToString(), pramlist.ToArray());
            List<Carriers> lis = new List<Carriers>();

            if (read.HasRows)
            {
                while (read.Read())
                {
                    Carriers carres = new Carriers();
                    carres.CarriesID = read["CarriesID"].ToString();
                    carres.LeaverDate = read["LeaverDate"].ToString();
                    carres.zhiDate = read["LeaverDate"].ToString();
                    carres.SendLinkman = read["SendLinkman"].ToString();
                    carres.SendCompany = read["SendCompany"].ToString();
                    carres.SendAddress = read["SendAddress"].ToString();
                    carres.ReceiveLinkman = read["ReceiveLinkman"].ToString();
                    carres.ReceiveCompany = read["ReceiveCompany"].ToString();
                    carres.TotalCost = read["TotalCost"].ToString();
                    carres.FinishedState = Convert.ToInt32(read["FinishedState"]);
                    carres.UserName = read["UserName"].ToString();
                    lis.Add(carres);
                }
            }

            return lis;

        }


        /// <summary>
        /// 调度任务
        /// </summary>
        /// <returns></returns>
        public string CarriesSching(int CarriesID,int TruckID,int UserID)
        {
            string sqlproc = "p_Carries_Sching";
            SqlParameter[] pram =
            {
                new SqlParameter("@CarriesID",SqlDbType.Int),
                new SqlParameter("@TruckID",SqlDbType.Int),
                new SqlParameter("@UserID",SqlDbType.Int),
                new SqlParameter("@Result",SqlDbType.VarChar,50)
            };
            pram[0].Value = CarriesID;
            pram[1].Value = TruckID;
            pram[2].Value = UserID;
            pram[3].Direction = ParameterDirection.Output;
            SqlDataReader read = DBHelper.Exreader(sqlproc, pram);
            read.Close();
            string Result = pram[3].Value.ToString();
            return Result;
        }



        /// <summary>
        /// 签收承运单
        /// </summary>
        /// <param name="carriesid"></param>
        /// <returns></returns>
        public int SignCarries(int carriesid)
        {
            string sqlproc = "p_SignCarries";
            SqlParameter[] pram =
            {
                new SqlParameter("@Carried",SqlDbType.Int)
            };
            pram[0].Value = carriesid;
            int num = DBHelper.Number(sqlproc, pram);
            return num;
        }

    }
}
